package 数据库.day2;

import 数据库.练习.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class Inquiry {
    public static void main(String[] args) {
        Scanner in=new Scanner(System.in);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Object>list=new ArrayList<>();
        String sql="select * from s_student where 1=1";
        System.out.println("请输入姓名：");
        String name=in.nextLine();
        System.out.println("请输入QQ：");
        String qqh=in.nextLine();
        System.out.println("请输入手机号：");
        String phone=in.nextLine();
        System.out.println("请输入看第几页：");
        int pageNo=in.nextInt();
        System.out.println("请输入一页几条：");
        int pageSize=in.nextInt();
        if (name!=null&&!name.equals("")){
            sql+=" and  sname like concat('%',?,'%')";
            list.add(name);
        }
        if (qqh!=null&&!qqh.equals("")){
            sql+=" and  qq like concat('%',?,'%')";
            list.add(qqh);
        }
        if (phone!=null&&!phone.equals("")){
            sql+=" and telephone  like concat('%',?,'%')";
            list.add(phone);
        }
        if (pageNo>=1&&pageSize>0){
            sql+=" limit ?,?";
            list.add((pageNo-1)*pageSize);
            list.add(pageSize);
        }
        System.out.println(sql+">>>>>>>>>>>>>>>"+list);
        try {
            con = DBUtil.getConnection();
            ps=con.prepareStatement(sql);
            for (int i=0;i<list.size();i++){
                ps.setObject(i+1,list.get(i));
            }
            rs=ps.executeQuery();
            while (rs.next()){
                System.out.println("学号："+rs.getString("stuno")+"\t"+"姓名："+rs.getString("sname")+"\t"+"性别："+rs.getString("sex")
                +"手机号："+rs.getString("telephone")+"\t"+"qq号："+rs.getString("qq"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,con);
        }
    }
}
